package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 刘柄华
 * Date: 2022-06-08
 * Time: 12:32
 */
public class BlogDao {

    // 通过这个 类 来封装对于 Blog 表的操作
    // 1. 新增一个博客
    // 2. 获取到所有博客列表.
    // 3. 根据博客 id 获取到指定博客.
    // 4. 根据博客 id 删除博客~
    // 5. 根据用户id 查找属于该用户的所有博客的数目

    public void insert(Blog blog){
        System.out.println("插入一篇博客");

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            // 1.建立连接
            connection = DBUtil.getConnection();
            // 2.构造sql语句
            String sql = "insert into blog values(null, ?, ?, ?, ?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,blog.getTitle());
            statement.setString(2,blog.getContent());
            statement.setTimestamp(3,blog.getPostTime());
            statement.setInt(4,blog.getUserId());
            // 3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1){
                System.out.println("插入博客成功");
            }else {
                System.out.println("插入博客失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
    }

    public List<Blog> selectAll(){
        System.out.println("查找所有博客");
        List<Blog> blogs = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.建立连接
            connection = DBUtil.getConnection();
            // 2.拼装SQL
            String sql = "select * from blog order by postTime desc";
            statement = connection.prepareStatement(sql);
            // 3.执行SQL
             resultSet = statement.executeQuery();
            // 4.遍历结果集合
            while(resultSet.next()){
                Blog blog = new Blog();
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                String content =  resultSet.getString("content");
                if(content.length()>90){
                    content = content.substring(0,90)+"...";
                }
                blog.setContent(content);
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                blogs.add(blog);
            }
            return blogs;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 5.释放资源
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    public Blog selectOne(int blogId){
        System.out.println("查找指定博客");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getConnection();

            String sql = "select * from blog where blogId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, blogId);

            resultSet = statement.executeQuery();
            // 4. 遍历结果集合, 由于此处是基于 自增主键 来查询的. 查询结果要么是 1 条记录, 要么是 0 条记录
            if (resultSet.next()) {
                Blog blog = new Blog();
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                return blog;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    public void delete(int blogId){
        System.out.println("删除指定博客");
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = DBUtil.getConnection();

            String sql = "delete from blog where blogId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,blogId);

            int ret = statement.executeUpdate();
            if(ret == 1){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
    }

    public int selectNum(int userId){
        System.out.println("查找该用户的博客的数目");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getConnection();

            String sql = "select count(*) as total from blog where userId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,userId);

            resultSet = statement.executeQuery();
            int num = 0;
            while (resultSet.next()) {
                num = resultSet.getInt("total");
            }
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static void main(String[] args) {
        BlogDao blogDao = new BlogDao();

//        // 1. 测试一下插入功能
//        Blog blog = new Blog();
//        blog.setTitle("张三(解说)");
//        blog.setContent("西班牙球员们尽管赢球但是心情依旧比较沮丧，但是他们完全不应为此感到抱歉。因为他们已经到过很多人永远无法企及的高度。作为他们的球迷当然是得到满足还会想要更多的满足，这无可厚非，但是这种要求并不实际，因为自古打天下难，守天下更难，没有人可以永远站在顶峰。即使可以做到居安思危未雨绸缪，但是你身边全都是和你一样充满野心、充满激情和充满渴望的年轻人，他们可以把你的长处和短处放在显微镜下去研究，以你为标靶，你说守天下难不难？人生当中成功只是一时的，失败却是主旋律，但是如何面对失败却把人分成了不同的样子。有的人会被失败击垮，有的人会不断爬起来继续向前，澳大利亚队是如此，西班牙队也是如此。我想真正的成熟应该并不是追求完美，而是直面自己的缺憾，这才是生活的本质。罗曼-罗兰说过：“这个世界上只有一种真正的英雄主义，那就是认清生活的真相并且仍然热爱它。”西班牙队重头再来吧，难道向上攀爬的那条路不是比站在顶峰更令人热血澎湃吗？相信拉莫斯他们还有更多的机会，就是这样。观众朋友们，再见。");
//        blog.setUserId(1);
//        blog.setPostTime(new Timestamp(System.currentTimeMillis()));
//        blogDao.insert(blog);

//        Blog blog = blogDao.selectOne(1);
//        System.out.println(blog);


    }

}
